package com.shukesmart.speech.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;

public class NoteDao {
    private NoteDBHelper helper;

    private SQLiteDatabase db;

    public NoteDao(Context context) {

        helper = new NoteDBHelper(context);

    }

    public void insert(NoteBean mDistanceInfo) {

        if (mDistanceInfo == null) {

            return;

        }


        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime currentDateTime = LocalDateTime.now();
        String dateTimeString = currentDateTime.format(formatter);
        db = helper.getWritableDatabase();

        String sql = "INSERT INTO userNote(createDate,context,title) VALUES('" + dateTimeString + "','" + mDistanceInfo.getContext() + "','" + mDistanceInfo.getTitle() + "')";
Log.i("sql",sql);

        db.execSQL(sql);

        db.close();


    }

    public void del(int id) {


        db = helper.getWritableDatabase();

        String sql = "delete  from  userNote where id = " + id;


        db.execSQL(sql);

        db.close();

    }

    public int getMaxId() {

        db = helper.getReadableDatabase();

        Cursor cursor = db.rawQuery("SELECT MAX(id) as id from userNote", null);

        if (cursor.moveToFirst()) {

            return cursor.getInt(cursor.getColumnIndexOrThrow("id"));

        }

        return -1;

    }


    public synchronized int insertAndGet(NoteBean mDistanceInfo) {

        int result = -1;

        insert(mDistanceInfo);

        result = getMaxId();

        return result;

    }

    /**
     * 根据id获取
     *
     * @param id
     * @return
     */

    public NoteBean getById(int id) {

        db = helper.getReadableDatabase();

        Cursor cursor = db.rawQuery("SELECT * from userNote WHERE id = ?", new String[]{String.valueOf(id)});

        NoteBean mDistanceInfo = null;

        if (cursor.moveToFirst()) {

            mDistanceInfo = new NoteBean();

            mDistanceInfo.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));
            mDistanceInfo.setTitle(cursor.getString(cursor.getColumnIndexOrThrow("title")));
            mDistanceInfo.setContext(cursor.getString(cursor.getColumnIndexOrThrow("context")));
            mDistanceInfo.setCreateDate(cursor.getString(cursor.getColumnIndexOrThrow("createDate")));


        }

        cursor.close();

        db.close();

        return mDistanceInfo;

    }


    /**
     * 更新记录
     *
     * @param account 要更新的对象
     */
    public void update(NoteBean account) {
        // 获取SQLiteDatabase对象
        SQLiteDatabase db = helper.getWritableDatabase();
        // 执行一条SQL语句
        String sql = "update userNote set title=?,context=? where _id=?";
        db.execSQL(sql, new Object[]{account.getContext(), account.getContext(), account.getId()});
        // 关闭
        db.close();
    }


    public ArrayList<NoteBean> getList(int pageNum) {
        ArrayList<NoteBean> list = new ArrayList<NoteBean>();
        String index = (pageNum - 1) * 20 + ""; // 翻页时的起始索引
        String count = 20 + ""; // 查询多少条数据

        db = helper.getReadableDatabase();

        String sql = "select * from userNote order by createDate desc limit ?,?";
        Cursor cursor = db.rawQuery(sql, new String[]{index, count});

        while (cursor.moveToNext()) {

            NoteBean mDistanceInfo = new NoteBean();

            mDistanceInfo.setId(cursor.getInt(cursor.getColumnIndexOrThrow("id")));
            mDistanceInfo.setTitle(cursor.getString(cursor.getColumnIndexOrThrow("title")));
            mDistanceInfo.setContext(cursor.getString(cursor.getColumnIndexOrThrow("context")));
            mDistanceInfo.setCreateDate(cursor.getString(cursor.getColumnIndexOrThrow("createDate")));
            list.add(mDistanceInfo);
        }

        cursor.close();

        db.close();

        return list;

    }

    /**
     * 查询所有记录的总数
     *
     * @return 所有记录总数
     */
    public int queryCount() {
        int count = -1;
        SQLiteDatabase db = helper.getReadableDatabase();
        String sql = "select count(*) from userNote";
        Cursor cursor = db.rawQuery(sql, null);
        cursor.moveToNext();
        count = cursor.getInt(0);
        cursor.close();
        db.close();
        return count;
    }

}